import { Model } from "./Model";
import { M, array_columns, mkdirs, parse_name } from "./Functions";
import { IServers, IDBMySqlTable, QueryTypes, IConfig, IDBMySqlProcedure, IDBMySqlFunction, IDBMySqlEvent } from '../interface/IFace';
import * as Sequelize from 'sequelize'
import * as moment from 'moment'
import * as fs from 'fs'
import * as path from 'path'
import * as _ from 'lodash'
import { md5 } from "./Crypt";
import * as Q from 'q'
/**
 * 获取数据库中的表列表
 * @param Config 
 */
export async function get_table_view_list(Config:IServers,WithPrefix:boolean=true):Promise<Array<IDBMySqlTable>>{
    let Column = `Tables_in_${Config.database.name}`
    let rs:Array<IDBMySqlTable> =  await query(Config,`SHOW TABLE STATUS WHERE NAME LIKE "${Config.database.prefix}%"`)
    if(WithPrefix===false){
        for(let i in rs){
            rs[i].Name = rs[i].Name.replace(Config.database.prefix,"")
        }
    }
    return rs;
}
/**
 * 直接执行一个Query查询
 * @param Config 
 * @param SQL 
 */
export async function query(Config:IServers,SQL:string):Promise<Array<any>> {
    return exec(Config,SQL,QueryTypes.SELECT)
}
/**
 * 执行SQL
 * @param Config 
 * @param SQL 
 * @param QueryType 
 */
export async function exec(Config:IServers,SQL:string,QueryType:QueryTypes):Promise<Array<any>> {
    let model = M("",Config);
    return model.query(SQL,{type:QueryType})
}
export async function backup(Config:IServers,Dir:string="",NoData=false) {
    if(Dir.length==0){
        Dir = Config.backup && Config.backup.database && Config.backup.database.dir ?(Config.backup.database.dir):(path.join(Config.root,'Backup'));
    }
    mkdirs(Dir);
    let Filename = Config.database.name + moment().format("_YYYY_MM_DD")+".sql"
    let TablesAndViews = await get_sorted_table(Config);
    let Procedures = await get_procedures(Config);
    let Functions = await get_functions(Config);
    let Events = await get_events(Config);
    try{
        let w = fs.createWriteStream(path.join(Dir,Filename))
        w.write("SET FOREIGN_KEY_CHECKS=0;\r\n")
        //TODO 整理数据表之间的关系，按关系进行排序    
        for(let d of TablesAndViews){
            let DDL = await get_table_ddl(Config,d.Name);
            w.write(`DROP ${d.Comment == "VIEW" ? "VIEW" : "TABLE"} IF EXISTS \`${d.Name}\`;\r\n`)
            w.write(DDL+";\r\n")
            if(!NoData && d.Comment != "VIEW"){
                for(let i=0;i<d.Rows;i+=20000){
                    w.write(await get_table_insert_sql(Config,d.Name,i,20000)+"\r\n")
                }
            }
        }
        for(let d of Functions){
            w.write(`DROP FUNCTION IF EXISTS \`${d.Name}\`;\r\n`)
            w.write(await get_function_ddl(Config,d.Name) + ";\r\n")
        }
        for(let d of Procedures){
            w.write(`DROP PROCEDURE IF EXISTS \`${d.Name}\`;\r\n`)
            w.write(await get_procedure_ddl(Config,d.Name) + ";\r\n")
        }
        for(let d of Events){
            w.write(`DROP EVENT IF EXISTS \`${d.EVENT_NAME}\`;\r\n`)
            w.write(await get_event_dll(Config,d.EVENT_NAME) + ";\r\n")
        }
        w.close();
    }catch(e){
        console.error(e.stack)
        return false;
    }finally{
        return true;
    }
}
export function db(config:IServers){
    if(!config._DBInstance){
        config._DBInstance = new Sequelize(config.database.name||"test",config.database.user||"root",config.database.pwd||"",{
            host:config.database.host||"localhost",
            port:config.database.port||3306,
            dialect:"mysql",
            operatorsAliases:false,
            pool:{
                max:50,
                min:1,
                idle:10000
            }
        });
    }
    return config._DBInstance;
}
/**
 * 获取排好顺序的数据表顺序
 * @param config 
 */
export async function get_sorted_table(config:IServers):Promise<Array<IDBMySqlTable>> {
    let Tables:Array<IDBMySqlTable> = await get_table_view_list(config)
    let DDLs:Array<string>=[];
    for ( let table of Tables){
        let DDL = await get_table_ddl(config,table.Name)
        if(DDL)
            DDLs.push(DDL)
    }
    let DDLStr = DDLs.join("\r\n");
    for ( let i in Tables){
        let rs:null|Array<string> = DDLStr.match(new RegExp(`\`${Tables[i].Name}\``,'g'));
        Tables[i].Count = rs?rs.length:0
        if(Tables[i].Comment=="VIEW"){
            Tables[i].Count += 100
        }
    }
    Tables = _.sortBy(Tables,['Count'],'desc')
    return Tables;
}
/**
 * 获取表DDL数据
 * @param config 
 * @param TableName 
 */
export async function get_table_ddl(config:IServers,TableName:string):Promise<string> {
    let rs = await query(config,`SHOW CREATE TABLE ${TableName}`)
    return rs.length>0?(rs[0]['Create Table']?rs[0]['Create Table']:rs[0]['Create View']):"";
}
/**
 * 获取表的插入数据
 * @param config 
 * @param TableName 
 */
export async function get_table_insert_sql(config:IServers,TableName:string,Start:number=0,End:number=10000):Promise<string> {
    //TODO 查询时切分数量
    let data = await query(config,`SELECT * FROM ${TableName} LIMIT ${Start},${End}`)
    if(data.length==0)return '';
    data = _.chunk(data,1000)
    let SQL = [];
    for(let d of data){
        let ds = [];
        for(let r of d){
            let str=[];
            for(let k in r){
                if(r[k] instanceof Date){
                    str.push(`'${moment(r[k]).format('YYYY-MM-DD HH:mm:ss')}'`)
                }else if(_.isNumber(r[k])){
                    str.push(r[k])
                }else if(_.isNull(r[k])){
                    str.push('null')
                }else{
                    str.push(`'${r[k].replace(/'/g,"\\'")}'`)
                }
            }
            ds.push(str.join())            
        }
        let s = `INSERT INTO ${TableName}(\`${Object.keys(d[0]).join('`,`')}\`) VALUES(${ds.join("),(")});`;
        SQL.push(s)
    }
    return SQL.join("\r\n");
}
/**
 * 获取存储过程列表
 * @param config 
 */
export async function get_procedures(config:IServers):Promise<Array<IDBMySqlProcedure>> {
    return await query(config,`SHOW PROCEDURE STATUS WHERE Db='${config.database.name}'`)
}
/**
 * 获取函数列表
 * @param config 
 */
export async function get_functions(config:IServers):Promise<Array<IDBMySqlFunction>> {
    return await query(config,`SHOW FUNCTION STATUS WHERE Db='${config.database.name}'`)
}
/**
 * 获取存储过程DDL
 * @param config 
 * @param ProcedureName 
 */
export async function get_procedure_ddl(config:IServers,ProcedureName:string):Promise<string> {
    let rs = await query(config,`SHOW CREATE PROCEDURE \`${ProcedureName}\``)
    if(rs.length>0){
        return rs[0]['Create Procedure']
    }
    return "";
}
/**
 * 获取函数DDL
 * @param config 
 * @param FunctionName 
 */
export async function get_function_ddl(config:IServers,FunctionName:string):Promise<string>{
    let rs = await query(config,`SHOW CREATE FUNCTION \`${FunctionName}\``)
    if(rs.length>0){
        return rs[0]['Create Function']
    }
    return "";
}
/**
 * 获取数据库中的事件列表
 * @param config 
 */
export async function get_events(config:IServers):Promise<Array<IDBMySqlEvent>> {
    return await query(config,`SELECT EVENT_CATALOG, EVENT_SCHEMA, EVENT_NAME, DEFINER, EVENT_DEFINITION, EVENT_TYPE, SQL_MODE, STATUS, EXECUTE_AT, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, ON_COMPLETION, CREATED, LAST_ALTERED, LAST_EXECUTED, EVENT_COMMENT FROM information_schema.EVENTS WHERE EVENT_SCHEMA='${config.database.name}' ORDER BY EVENT_NAME`)
}
/**
 * 获取数据库中的事件DDL
 * @param config 
 * @param EventName 
 */
export async function get_event_dll(config:IServers,EventName:string):Promise<string>{
    let rs = await query(config,`SHOW CREATE EVENT \`${EventName}\``);
    if(rs.length>0){
        return rs[0]['Create Event']
    }
    return '';
}

export async function generate_sequelize_define_from_db(config:IServers) {
    let tables = await get_table_view_list(config);
    for (let d of tables){
        let TableName = parse_name(d.Name);
        
    }
}